package com.monitorRoutine.monitorRoutineServer.service.impl;


import com.alibaba.fastjson.JSON;
import com.monitorRoutine.monitorRoutineServer.entity.ControlCommand;
import com.monitorRoutine.monitorRoutineServer.entity.SqlRecord;
import com.monitorRoutine.monitorRoutineServer.entity.SqlResult;
import com.monitorRoutine.monitorRoutineServer.entity.SqlResultVo;
import com.monitorRoutine.monitorRoutineServer.mapper.SqlRecordMapper;
import com.monitorRoutine.monitorRoutineServer.mapper.SqlResultMapper;
import com.monitorRoutine.monitorRoutineServer.service.ControlCommandService;
import com.monitorRoutine.monitorRoutineServer.service.MonitorSqlService;
import com.monitorRoutine.monitorRoutineServer.service.SqlRecordService;
import com.monitorRoutine.monitorRoutineServer.service.SqlResultService;
import com.monitorRoutine.monitorRoutineServer.util.ExcelUtil;
import com.monitorRoutine.monitorRoutineServer.util.TimeUtil;
import lombok.extern.java.Log;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.dao.DataAccessException;
import org.springframework.data.redis.core.RedisOperations;
import org.springframework.data.redis.core.RedisTemplate;
import org.springframework.data.redis.core.SessionCallback;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.time.Instant;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.time.format.DateTimeFormatter;
import java.util.*;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.Executors;
import java.util.concurrent.TimeUnit;

@Slf4j
@Service
@Log
public class MonitorSqlServiceImpl implements MonitorSqlService {
    @Autowired
    SqlRecordMapper sqlRecordMapper;
    @Autowired
    SqlResultMapper sqlResultMapper;
    @Autowired
    SqlRecordService sqlRecordService;
    @Autowired
    private RedisTemplate<String, String> redisTemplate;
    @Autowired
    private SqlResultService sqlResultService;

    @Value("${file.savePath}")
    String SAVE_PATH;
    @Override
    public List<SqlResult> executeSql(List<SqlRecord> sqlRecords) {
        SqlRecord sqlRecord = sqlRecords.get(0);
       ArrayList<SqlResult> sqlResults = new ArrayList<>();

       LocalDateTime now = LocalDateTime.now();
        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
        String formattedDateTime = now.format(formatter);

        sqlRecord.setTimes(formattedDateTime);
        try {
            // 加载JDBC驱动
            Class.forName(sqlRecord.getDrive());

            // 建立数据库连接
            Connection conn = DriverManager.getConnection(sqlRecord.getUrl(), sqlRecord.getUser(), sqlRecord.getPassword());

            // 创建Statement对象
            Statement stmt = conn.createStatement();

            for (SqlRecord record : sqlRecords) {
                SqlResult sqlResult = new SqlResult();

                try {

                    sqlResult.setSqlRecordId(record.getSqlRecordId());
                    sqlResult.setSetname(record.getSetname());
                    sqlResult.setTime(new Date());
                    // 执行SQL查询语句
                    ResultSet rs = stmt.executeQuery(record.getExecutesql());
                    // 获取所有列名
                    List<String> columnNames = new ArrayList<>();
                    for (int i = 1; i <= rs.getMetaData().getColumnCount(); i++) {
                        columnNames.add(rs.getMetaData().getColumnName(i));
                    }
                    String columnNameS = JSON.toJSONString(columnNames);
                    sqlResult.setSqlField(columnNameS);
                    ArrayList<ArrayList<String>> table = new ArrayList<>();
                    while (rs.next()) {
                        int columnCount = rs.getMetaData().getColumnCount();
                        ArrayList<String> ranks = new ArrayList<>();
                        for (int i = 1; i <= columnCount; i++) {
                            ranks.add(rs.getString(i));
                        }
                        table.add(ranks);
                    }
                    String s = JSON.toJSONString(table);
                    sqlResult.setSqlResult(s);
                    // 关闭资源
                    rs.close();
                } catch (Exception e) {
                    sqlResult.setSqlResult(JSON.toJSONString(e));
                }
               sqlResults.add(sqlResult);
            }


            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();

        }
        return sqlResults;
    }

    @Override
    public boolean addSqlRecord(SqlRecord sqlRecord) {
        return sqlRecordMapper.insert(sqlRecord) > 0;
    }

    public void scanSqlRecordDatabase() {
        ArrayList<String> strings = new ArrayList<>();
        strings.add("0");
        strings.add("2");
        List<SqlRecord> sqlRecords = sqlRecordMapper.selectSqlRecordStatus(strings);
        List<Object> results = redisTemplate.execute(new SessionCallback<List<Object>>() {
            @Override
            public List<Object> execute(RedisOperations operations) throws DataAccessException {
                // 开始管道
                operations.multi();

                // 在这里添加你的命令
                for (SqlRecord sqlRecord : sqlRecords) {
                    String[] timeArray = sqlRecord.getTimes().split(",");
                    for (String time : timeArray) {
                        redisTemplate.opsForList().rightPush(time, sqlRecord.getSqlRecordId());
                    }
                    sqlRecord.setStatus("1");
                }

                // 执行管道中的所有命令
                return operations.exec();
            }
        });
        boolean b = sqlRecordService.updateSqlRecord(sqlRecords);
    }

    @Override
    public void executeSqlTask() {
//        ExecutorService executor = Executors.newFixedThreadPool(5); // 创建一个包含5个线程的线程池

        LocalTime now = LocalTime.now();
//        LocalTime now = LocalTime.of(0, 0, 0);
//        LocalTime now = LocalTime.of(8, 30, 0);
//        LocalTime now = LocalTime.of(14, 30, 0);
//        LocalTime now = LocalTime.of(16, 30, 0);
//        LocalTime now = LocalTime.of(20, 30, 0);

        DateTimeFormatter formatter = DateTimeFormatter.ofPattern("HH:mm");
        String formattedTime = now.format(formatter);
        List<String> allElements = redisTemplate.opsForList().range(formattedTime, 0, -1);


        Instant startTime = Instant.now();
        if (allElements.size()==0){return;}
        log.info("sql定时任务数："+allElements.size()+"\n时间："+ TimeUtil.formatInstant(startTime, formatter));



        List<SqlRecord> sqlRecordList = sqlRecordService.getSqlRecordListById(allElements);
       Map<String, List<SqlRecord>> stringListMap = new HashMap<>();
       for (SqlRecord sqlRecord : sqlRecordList) {
          List<SqlRecord> sqlRecords = stringListMap.get(sqlRecord.getUrl()+sqlRecord.getUser()+sqlRecord.getPassword());
          if (sqlRecords==null) {
             sqlRecords=new ArrayList<SqlRecord>();
             stringListMap.put(sqlRecord.getUrl()+sqlRecord.getUser()+sqlRecord.getPassword(),sqlRecords);
          }
          sqlRecords.add(sqlRecord);
        }
        ArrayList<SqlResultVo> sqlResultVos = new ArrayList<>();
        for (String s : stringListMap.keySet()) {
          List<SqlRecord> sqlRecords = stringListMap.get(s);

//            executor.submit(() -> {
                // 这里写线程要执行的代码
                List<SqlResult> sqlResults = executeSql(sqlRecords);
                sqlResultService.saveSqlRecordList(sqlResults);
//            });

            sqlResultVos.addAll( SqlResultVo.SqlResultchangeVoList(sqlResults,sqlRecords,true));
        }
//        executor.shutdown(); // 关闭线程池
        LocalDateTime localDateTime = LocalDateTime.now();
        DateTimeFormatter formatter_nyr = DateTimeFormatter.ofPattern("YYYY_MM_dd");
        String formattedTime_nyr = localDateTime.format(formatter_nyr);


        ExcelUtil.addSqlResultToExcel(sqlResultVos,formattedTime,SAVE_PATH+""+formattedTime_nyr+".xlsx");


//        try {
//            executor.awaitTermination(1, TimeUnit.MINUTES); // 等待所有任务结束
//        } catch (Exception e) {
//            e.printStackTrace();
//        }

    }



}
